﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Text;
using System.Data;
namespace DataAccess
{
   public class DataFacade
    {
       //string connStr = @"Data Source=.;Initial Catalog=ClinicManagement;Integrated Security=True";
       //Hieu ConnectString
       string connStr = @"Data Source=HIEUDIEP\ENTERPRISE2005;Initial Catalog=ClinicManagement;Integrated Security=True";
       
        SqlConnection conn;
        DataSet ds;
        public DataFacade()
        {
            ds = new DataSet();
        }
        public SqlConnection getConnection()
        {
            SqlConnection conn = new SqlConnection(connStr);
            return conn;
        }

        public void FillData(string sql, DataSet d, String tbl)
        {
            conn = getConnection();
            SqlDataAdapter ad = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand(sql, conn);
            ad.SelectCommand = cmd;
            ad.Fill(d, tbl);
        }


        public DataTable getItemType()
        {
            try
            {

                conn = getConnection();
                String str = @"SELECT id,TypeName,parentId FROM tbl_ItemType";
                SqlDataAdapter ad = new SqlDataAdapter();
                SqlCommand cmd = new SqlCommand(str, conn);
                ad.SelectCommand = cmd;
                ad.Fill(ds, "tbl_ItemType");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return ds.Tables["tbl_ItemType"];
        }

        public DataTable getTop24ItemsMedicine()
        {
            String str = @"select top(24) ID,itemName,itemType,itemDescription,itemImage,
                        staff,producer,price,quantity,ItemStatus,
                         convert(varchar(50),uploadDate,103),ViewCount from tbl_item Where itemType=1 order by uploadDate asc ";
            FillData(str, ds, "tblTmp");
            conn.Close();
            return ds.Tables["tblTmp"];
        }
        public DataTable getTop24ItemsMachine()
        {
            String str = @"select top(24) ID,itemName,itemType,itemDescription,itemImage,
                        staff,producer,price,quantity,ItemStatus,
                         convert(varchar(50),uploadDate,103),ViewCount from tbl_item Where itemType=2 order by uploadDate asc ";
            FillData(str, ds, "tblTmp");
            conn.Close();
            return ds.Tables["tblTmp"];
        }

        public DataTable getItems()
        {
            String str = @"select ID,itemName,itemType,itemDescription,itemImage,
                        staff,producer,price,quantity,ItemStatus,
                         convert(varchar(50),uploadDate,103),ViewCount from tbl_item order by uploadDate asc";
            FillData(str, ds, "tblTmp");
            conn.Close();
            return ds.Tables["tblTmp"];
        }
        public DataTable getItemByItemType(String typeName)
        {
            String sql = @"SELECT ID FROM tbl_ItemType WHERE typeName = N'" + typeName + "'";
            FillData(sql, ds, "tblItemTypeBytypeName");
            int id = Convert.ToInt16(ds.Tables["tblItemTypeBytypeName"].Rows[0][0].ToString());

            String sqlItem = @"select ID,itemName,itemType,itemDescription,itemImage,
                        staff,producer,price,quantity,ItemStatus,
                         uploadDate,ViewCount from tbl_item WHERE itemType=" + id;
            FillData(sqlItem, ds, "tblItemByItemType");

            return ds.Tables["ItemByItemType"];
        }
       
      
    }
}
